﻿using System.Windows.Forms;
using Microsoft.VisualBasic;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;
using System.IO;
using System.Data.SqlTypes;

namespace DevEx.Thanh.Utilities
{
    class XL_DULIEU
    {
        public static string strKetNoi = global::DevEx.Properties.Settings.Default.QLVTConnectionString;
        public static void GanCauLenhKetNoi(string s)
        {
            strKetNoi = s;
        }
        //ham dung de doc du lieu va do vao mot bang
        public static bool LayDuLieuRaBang(string strCauLenh, ref DataTable bang)
        {
            bool kq = true;
            try
            {
                SqlDataAdapter adapt = new SqlDataAdapter(strCauLenh, strKetNoi);
                //do du lieu ra bang
                adapt.FillSchema(bang, SchemaType.Source);
                adapt.Fill(bang);
            }
            catch (Exception ex)
            {
                //ghi khong thanh cong
                kq = false;
            }
            return kq;
        }
        //ham dung de ghi du lieu cua bang vao co so du lieu chinh
        public static bool GhiBang(ref DataTable bang, string tenBang)
        {
            bool kq = true;
            string strCauLenh = "select * from " + tenBang;
            try
            {
                SqlDataAdapter adapt = new SqlDataAdapter(strCauLenh, strKetNoi);
                SqlCommandBuilder boDocGhi = new SqlCommandBuilder(adapt);
                //ghi bang vao co so du lieu
                adapt.Update(bang);
            }
            catch (Exception ex)
            {
                kq = false;
            }
            return kq;
        }

        //ham thuc hien mot lenh nao do cua SQL
        public static bool ThucHienLenh(string strCauLenh)
        {
            bool kq = true;

            //tao ra cau lenh de thuc hien
            SqlCommand cmd = new SqlCommand();
            SqlConnection cnKetNoi = new SqlConnection(strKetNoi);
            cmd.Connection = cnKetNoi;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strCauLenh;
            try
            {
                //thuc hien lenh
                cnKetNoi.Open();
                cmd.ExecuteNonQuery();
                cnKetNoi.Close();
            }
            catch (Exception ex)
            {
                kq = false;
            }
            return kq;
        }

        //lay gia tri cua viec thuc thi mot Store procedure
        public static object LayGiaTri(string strCauLenh)
        {
            object kq = null;

            //tao ra cau lenh de thuc hien
            SqlCommand cmd = new SqlCommand();
            SqlConnection cnKetNoi = new SqlConnection(strKetNoi);
            cmd.Connection = cnKetNoi;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = strCauLenh;

            try
            {
                //thuc hien lenh
                cnKetNoi.Open();
                kq = cmd.ExecuteScalar();
                cnKetNoi.Close();
            }
            catch (Exception ex)
            {
                kq = null;
            }
            return kq;
        }
        //lay Connection String tu file UDL
        public static string LayConnectionString(string fileName)
        {
            string mCnnStr = "";
            try
            {
                StreamReader sr = new StreamReader(fileName);
                string line = null;
                do
                {
                    line = sr.ReadLine();
                    if ((line != null))
                    {
                        string str = "Provider=SQLOLEDB.1;";
                        int index = line.IndexOf(str) + str.Length;
                        if (index == str.Length)
                        {
                            mCnnStr = line.Substring(index);
                            break; // TODO: might not be correct. Was : Exit Do
                        }
                    }
                } while (!(line == null));
                sr.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Không đọc được dữ liệu! Lỗi", "Thông báo", MessageBoxButtons.OK);
            }
            return mCnnStr;
        }

        public static string GetConnectStrFromFile(string pFileSpec)
        {
            string mPassFromFile = "";
            string mCnnStr = "";
            try
            {
                StreamReader sr = new StreamReader(pFileSpec);
                string line = null;
                int l = 0;
                do
                {
                    line = sr.ReadLine();
                    l += 1;
                    if ((l == 3))
                    {
                        mCnnStr = line;
                        break; // TODO: might not be correct. Was : Exit Do
                    }
                } while (!(line == null));
                sr.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return mCnnStr;
        }
        public static string LayGiaTriThanhPhan(string sTenThanhPhan)
        {
            //tuy theo gia tri ma phai chuyen ve dung dang cua no
            string kq = "";
            int vt1 = 0;
            int vt2 = 0;
            dynamic temp = sTenThanhPhan + "=";
            //vi tri 1 se la vi tri cua Cum tu Intital Catalog=
            vt1 = strKetNoi.IndexOf(temp);
            //vi tri 2 se la vi tri cua dau; ket thuc cho cum "Initial Catalog=<ten DB>;"
            vt2 = strKetNoi.IndexOf(";", vt1);
            kq = strKetNoi.Substring(vt1 + temp.Length, vt2 - vt1 - temp.Length);
            kq = kq.Trim();
            return kq;
        }
    }
}
